﻿Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports Microsoft.Win32
Imports System.IO

Public Class NewRecord_Form
    Dim jobID As Integer = 0
    Private Sub Btn_AddRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_AddRecord.Click
        Me.Cursor = Cursors.WaitCursor
        If TB_JobID.Text = "" Then
            GoTo Finish
        End If

        'Query DB and find next job id
        ESBD_Form.OpenSQLConnection()
        Dim conn As New SqlConnection(csb.ToString())

        'Construct Insert statement
        Dim Insert_Stmt As String = "INSERT INTO ESBD_Main ([Job_ID], [Project_Name], [Requested_Date], [Completion_Date], [Ship_Date], [Build_Quantity], [Build_Type], [BOM_Rev], [PCB_Rev], [PCB_ZNN], [PCB_Manufacturer], [PCB_Assembler], [LED_Specification], [Special_Build_Instruction], [Customer], [Shipping_Record], [Status], [Comments]) VALUES ('" + jobID.ToString + "','" + TB_ProjName.Text + "','" + TB_ReqDate.Text + "','" + TB_CompDate.Text + "','" + TB_ShipDate.Text + "','" + TB_BuildQty.Text + "','" + TB_BuildType.Text + "','" + TB_BOMRev.Text + "','" + TB_PCBRev.Text + "','" + TB_PCBZNN.Text + "','" + TB_PCBManu.Text + "','" + TB_PCBAssembler.Text + "','" + TB_LEDSpec.Text + "','" + TB_BuildIns.Text + "','" + TB_Customer.Text + "','" + TB_AttachFile.Text + "','" + TB_Status.Text + "','" + TB_Comments.Text + "')"

        Try
            cmd.Connection = conn
            conn.Open()
            cmd.CommandText = Insert_Stmt
            cmd.ExecuteScalar()
            conn.Close()

            'Attach file if it exists
            If TB_AttachFile.Text <> "" Then
                'Upload file to Database
                Dim fs As New FileStream(Shipping_Record.AttachFile, FileMode.Open, FileAccess.Read)
                Dim br As New BinaryReader(fs)
                Dim numBytes As Integer = New FileInfo(Shipping_Record.AttachFile).Length
                Dim buff As Byte() = br.ReadBytes(numBytes)

                Try
                    ESBD_Form.OpenSQLConnection()
                    Dim connection As New SqlConnection(csb.ToString())
                    cmd.Connection = connection
                    connection.Open()

                    cmd.CommandText = "INSERT INTO Attachments_TB ( [FileName], [Extension], [Content] ) VALUES('" + Shipping_Record.Attach_FileName + "', '" + Shipping_Record.Attach_File_Ext + "', @Content);"
                    cmd.Parameters.Add(New SqlParameter("@Content", buff))

                    cmd.ExecuteNonQuery()

                    numBytes = 0
                    buff = Nothing
                    cmd.Parameters.Clear()

                    connection.Close()
                    Me.DialogResult = System.Windows.Forms.DialogResult.OK
                    Me.Close()
                Catch ex As Exception
                    Dim connection As New SqlConnection(csb.ToString())
                    connection.Close()
                    MsgBox(ex.Message)
                End Try
            Else
                'Do Nothing
            End If

            MsgBox("New record has been added to the table.", MsgBoxStyle.Information Or MsgBoxStyle.OkOnly, "New Record Added!")

            'Clear all fields
            TB_JobID.Text = ""
            TB_BOMRev.Text = ""
            TB_BuildIns.Text = ""
            TB_BuildQty.Text = ""
            TB_BuildType.Text = ""
            TB_Comments.Text = ""
            TB_CompDate.Text = ""
            TB_PCBZNN.Text = ""
            TB_Customer.Text = ""
            TB_LEDSpec.Text = ""
            TB_PCBAssembler.Text = ""
            TB_PCBManu.Text = ""
            TB_PCBRev.Text = ""
            TB_ProjName.Text = ""
            TB_ReqDate.Text = ""
            TB_ShipDate.Text = ""
            TB_Status.Text = ""
            TB_AttachFile.Text = ""
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
Finish:
        Me.Cursor = Cursors.Default

    End Sub

    Private Sub Btn_CancelAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_CancelAdd.Click
        Me.DialogResult = System.Windows.Forms.DialogResult.Cancel
        Me.Close()
    End Sub

    Private Sub NewRecord_Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Disable all combobox fields
        TB_ProjName.Enabled = False
        TB_BuildQty.Enabled = False
        TB_PCBManu.Enabled = False
        TB_PCBAssembler.Enabled = False
        TB_Customer.Enabled = False
        TB_Status.Enabled = False
        TB_JobID.Enabled = False
        TB_AttachFile.Enabled = False

        'Find Next JobID
        ESBD_Form.OpenSQLConnection()
        Dim conn As New SqlConnection(csb.ToString())
        Try

            Dim SQLResult As Object
            Dim Result As Integer = 0
            cmd.Connection = conn
            conn.Open()
            cmd.CommandText = "Select MAX(Job_ID) from ESBD_Main"

            SQLResult = cmd.ExecuteScalar
            If Convert.IsDBNull(SQLResult) Then
                SQLResult = 1
            Else
                Result = SQLResult
            End If
            jobID = Result + 1
            TB_JobID.Text = jobID.ToString
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.message + vbCrLf + vbCrLf + "Connection Failed")
            conn.Close()
            Me.Cursor = Cursors.Default
        End Try
    End Sub
End Class